import pymysql

# coding:utf-8
import pandas as pd

import re
import numpy as np

# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="hui123456", db='dbtest')
# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

sql = """
      SELECT DISTINCT Product_BianMa, COUNT(*)
      FROM doudian_day
      WHERE MONTH (day) = 10
      GROUP BY Product_BianMa 
      """

# 天猫等平台
sql1 = """
       SELECT DISTINCT Product_BianMa, COUNT(*)
       FROM niandu_day
       WHERE MONTH (day) = 10
       GROUP BY Product_BianMa 
       """

cursor.execute(sql)
result_ = cursor.fetchall()
result_d = pd.DataFrame(list(result_), columns=['商家编码', '个数'])

cursor.execute(sql1)
result_tm = cursor.fetchall()
result_tmm = pd.DataFrame(list(result_tm), columns=['商家编码', '个数'])

result = pd.concat([result_d, result_tmm])

pp = pd.read_excel(r'G:\工作\商家编码匹配产品 -4月财务.xlsx')  # 匹配表
# pp=pd.read_excel(r'G:\工作\商家编码匹配产品 -月总.xlsx')  #匹配表
# 分列
s_1 = []  # 分列后以列表形式保存
r_1 = []  # 添加每个分列后的产品
r_3 = []  # 添加商品数量
r_4 = []  # 添加平台
r_5 = []  # 添加SKU
for date_s in list(result['商家编码']):
    if '+' in date_s:
        date_split = str(date_s).split('+')
        s_1.append(date_split)

    elif '，' in date_s:
        date_split = str(date_s).split('，')
        if '赠' in date_split[0]:
            date_split_1 = date_split[0].split('赠')
            s_1.append(date_split_1)
        else:
            s_1.append([date_split[0]])


    elif '赠' in date_s:

        date_split = str(date_s).split('赠')
        s_1.append(date_split)


    elif '＋' in date_s:
        date_split = str(date_s).split('＋')
        s_1.append(date_split)
    else:
        s_1.append([date_s])

result['编码分裂'] = s_1
date_concat_s = result.reset_index(drop=True)

n = 0
for i in list(date_concat_s['编码分裂']):

    # 查i对应的索引值
    # n=list(date_concat['编码分裂']).index(i)
    # 分裂后生成的是列表，所以用列表长度判定
    if len(i) == 1:  # 判定是否有多的订单
        r_1.append(i[0])

        r_5.append(list(date_concat_s['商家编码'])[n])

    else:  # 多订单的情况下分行并对应单量
        for c in i:
            r_1.append(c)

            r_5.append(list(date_concat_s['商家编码'])[n])

    n += 1
s = {'SKU': r_5, '商家编码': r_1}  # 商家编码是分列后的编码
res = pd.DataFrame(s)

res_pp = pd.merge(res, pp, on=['商家编码'], how='left')


# 提取商品袋数
def d(x):
    if '袋' in x:
        try:

            return re.findall(r"(\d+)袋", x)[-1]
        except:
            return 0

    elif '桶' in x:
        try:

            return re.findall(r"(\d+)桶", x)[-1]
        except:
            return 0
    elif ('个' in x):
        try:

            return re.findall(r"(\d+)个", x)[-1]
        except:
            return 0
    elif '包' in x:
        try:

            return re.findall(r"(\d+)包", x)[-1]
        except:
            return 0

    elif ('只' in x):

        try:

            return re.findall(r"(\d+)只", x)[-1]
        except:
            return 0
    elif ('根' in x):

        try:

            return re.findall(r"(\d+)根", x)[-1]
        except:
            return 0
    else:

        return 0


res_pp['件数'] = res_pp['商家编码'].map(d)
res_pp['件数'] = res_pp['件数'].astype(int)
res_pp['件数1'] = np.where(res_pp['单位'] == '总', 1, res_pp['件数'])

res_pp['成本'] = res_pp['件数1'] * res_pp['成本价']
res_pp.to_excel(r'G:\工作\SKU利润测算\2025年\10月\10月SKU成本全平台--财务.xlsx')

# 注意
# 1. 需要把 "G:\工作\商家编码匹配产品 -4月财务.xlsx"  里面的 生产成本 加上（最近的月份的）
# 2. sheet 中 也需要把新增的产品加上
